﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Xml;
using System.Data;
using Microsoft.Office.Core;
using System.Windows.Forms;
using System.Reflection;//Excel相关命名空间
using System.Runtime.InteropServices;//Excel相关命名空间

namespace UsrCustomFun.ClassExcel
{
    /// <summary>
    /// 生成Excel文件
    /// </summary>
    class DataToExcel
    {
        /// <summary>
        /// DataTable数据生成Excel表
        /// </summary>
        /// <param name="dtExcel">DataTable</param>
        /// <param name="Path">Excel存储路径</param>
        /// <param name="filename">保存的文件名</param>
        public static void DtToExcel(DataTable dtExcel, string Path, string filename)
        {
            try
            {
                if (File.Exists(Path + "\\" + filename + ".xls"))
                {
                    File.Delete(Path + "\\" + filename + ".xls");
                }
                //创建Excel应用程序
                Excel.Application xApp = new Excel.ApplicationClass();

                if (xApp == null)
                {
                    UserMessageBox.UsrShow("错误：Excel不能打开！", "系统提示");
                    return;
                }
                object objOpt = Missing.Value;
                Excel.Workbook xBook = xApp.Workbooks.Add(true);//添加新工作簿
                Excel.Sheets xSheets = xBook.Sheets;
                Excel._Worksheet xSheet = null;
                //
                //创建空的sheet
                //
                xSheet = (Excel._Worksheet)(xBook.Sheets.Add(objOpt, objOpt, objOpt, objOpt));

                if (xSheet == null)
                {
                    UserMessageBox.UsrShow("错误：工作表为空！", "系统提示");
                    return;
                }
                //写数据集表头
                for (int k = 0; k < dtExcel.Columns.Count; k++)
                    xSheet.Cells[1, k + 1] = dtExcel.Columns[k].ColumnName.ToString().Trim();
                //写数据集数据
                for (int i = 0; i < dtExcel.Rows.Count; i++)
                {
                    for (int j = 0; j < dtExcel.Columns.Count; j++)
                        xSheet.Cells[i + 2, j + 1] = dtExcel.Rows[i][j];
                }
                //保存文件
                xBook.Saved = true;
                xBook.SaveAs(Path + "\\" + filename + ".xls", objOpt, objOpt, objOpt, objOpt, objOpt, Excel.XlSaveAsAccessMode.xlShared, objOpt, objOpt, objOpt, objOpt, objOpt);
                //xBook.SaveCopyAs(Path + "\\" + filename);

                //不显示文件
                xApp.Visible = false;
                //
                //释放资源
                //
                Marshal.ReleaseComObject(xSheet);
                xSheet = null;
                Marshal.ReleaseComObject(xSheets);
                xSheets = null;

                Marshal.ReleaseComObject(xBook);
                xBook = null;
                xApp.Quit();
                Marshal.ReleaseComObject(xApp);
                xApp = null;
                GC.Collect();//强行销毁
            }
            catch (Exception ex)
            {
                UserMessageBox.UsrShow("写入Excel发生错误！","系统提示",ex.Message.ToString());
            }
        }
    }
}
